System and method for compilation of quickbooks accounts data

ABSTRACT

Embodiments include a method of aggregating data from a plurality of QuickBooks (QB) files that may be in physically separate locations, and having at least one account name in common. In one embodiment, the method includes, assigning a different parent account identification (ID) number to each differently named parent account. The method includes creating an entry in a hash table for each parent account ID, the contents of the entry being a collection of rows including a row for each different sub account of the parent account, wherein data for multiple instances of like named parent accounts are aggregated, the key of the entry in the hash table being the parent account ID.

FIELD

This written description is in the field of QuickBooks (QB) dataprocessing.

BRIEF DESCRIPTION OF THE DRAWINGS

Aspects of embodiments will become apparent upon reading the followingdetailed description and upon reference to the accompanying drawings inwhich like references may indicate similar elements:

FIG. 1A depicts an expense table for a first company A;

FIG. 1B depicts an expense table for a second company B;

FIG. 1C depicts an expense table that aggregates the expense data fromcompany A and company B;

FIG. 2A depicts a network and server for aggregating QB data fromcomputers of companies and sub companies.

FIG. 2B depicts a flow chart for setting up QB relationships;

FIG. 3 depicts a collection of the data from companies A and B obtainedfrom using Structure Query Language (SQL);

FIG. 4 depicts a table of parent accounts;

FIG. 5 depicts a collection of rows in a hash table for each parentaccount and each sub accounts and each sub sub account for the data fromcompanies A and B;

FIG. 6 depicts a flow chart for assigning IDs and creating hash tables;and

FIG. 7 depicts a flow chart for identifying and aggregating data oflike-named accounts.

DETAILED DESCRIPTION

The following is a detailed description of embodiments depicted in theaccompanying drawings. The amount of detail offered is not intended tolimit the anticipated variations of embodiments; but, on the contrary,the intention is to cover modifications, equivalents, and alternativesfalling within the scope of the appended claims. The detaileddescriptions below are designed to make such embodiments obvious to aperson of ordinary skill in the art.

Embodiments include a method of aggregating data from a plurality ofQuickBooks (QB) files that may be in physically separate locations, andhaving at least one account name in common. In one embodiment, themethod includes, assigning a different parent account identification(ID) number to each differently named parent account. The methodincludes creating an entry in a hash table for each parent account ID,the contents of the entry being a collection of rows including a row foreach different sub account of the parent account, wherein data formultiple instances of like named parent accounts are aggregated, the keyof the entry in the hash table being the parent account ID.

Another embodiment is a QuickBooks (QB) data aggregation system having aweb server connected to a network of computers having QB files stored ina database format at each computer. The server has a memory to storedata obtained from the QB files of the network of computers. The webserver also has a processor configured to identify like-named parentaccounts in the QB files. For each like-named parent account, theprocessor is configured to aggregate the data of like-named sub accountsof the parent account from each computer. The processor is furtherconfigured to store the aggregated data in a hash table. Each entry inthe hash table corresponds to data of a different-named sub account,each hash table entry having a collection of rows, a row for each subaccount of the parent account, and having columns of data associatedwith each sub account.

Another embodiment is a tangible storage medium having instructions thatwhen executed by a computer cause the computer to aggregate data of aplurality of QuickBooks (QB) files. The instructions cause the computerto obtain QB data from each of a plurality of computers in a network,the data being organized into one or more parent accounts. Theinstructions further cause the computer to aggregate the data fromlike-named sub accounts for each parent account. The instructionsfurther cause the computer to form a collection in a hash table for eachdifferently-named parent account, an entry including aggregate data oflike-named sub accounts associated with the parent account name.

Small businesses and accountants need visibility and usability offinancial and operational data from remote locations. QuickBooks™ (QB)by Intuit™ currently owns roughly 87% of the small business accountingsoftware installed at computers of companies distributed geographically.However, the QuickBooks software does not allow for aggregation andconsolidation of disparate files. Only their Enterprise™ solutionsupports this. However, the Enterprise solution involves difficultprocesses and only applies to Enterprise-formatted company files. MostQuickBooks users do not run Enterprise due to a lack of need for thefeatures it provides and because of its high cost. If a company hasmultiple QuickBooks files with users needing to access data contained inmore than one QB file, one option is to open each QB file, export alarge number of reports to Microsoft™ Excel™ and then map the accountnames of each QB file, combine the data, create custom analytics tomeasure results, create charts and graphs for better understanding andtransmit the results to users. This process is extremely manuallyintensive and in many cases prohibitive due to the large number of QBfiles that may exist. For instance, a franchise might have 50, 200 oreven 30,000 QB files in its network of businesses located across theglobe.

Further complicating the process is the fact that versions of QB are notbackward compatible. For example, if a user runs QB 2008 Premier™, thenopening a file in a later version of QB will convert the file to thelater version and the file can then not be opened using the earlierversion of QB.

FIG. 1A shows a table of expenses for a company A. There is a row foreach parent account: Hotel, Restaurants, and Transportation. For eachparent account there is a row for each sub account of the parentaccount. For example, the parent account, Hotel, has two sub accounts:Hyatt and Motel 6. In this example, the sub account, Motel 6, has twosub sub accounts: Dallas and Houston. Also, the table of FIG. 1A showsfive columns containing the corresponding data associated with each subaccount and each sub sub account. Although only a few parent accounts,sub accounts, and sub sub accounts are shown, more or less of theseaccounts may be given for a company QB file. Further, some company filesmay have sub sub sub accounts, or even deeper levels of sub accounts.

FIG. 1B shows a table of expenses for a company B. The structure of thetable is similar as for company A. That is, company B has the sameparent accounts and mostly the same sub accounts and sub sub accounts ashas company A.

Embodiments disclosed herein may aggregate the data of company A andcompany B as shown in FIG. 1C. The structure of the aggregate tableshown in FIG. 1C has the same structure as the structure of the table ofFIG. 1A and the table of FIG. 1B. However, the amount in a particularrow and column of the table of FIG. 1C is the sum of the amounts in thecorresponding row and column of FIG. 1A and FIG. 1B. The manner in whichthis aggregation is achieved is described below.

FIG. 2A depicts a network and server for aggregating QB data fromcomputers of companies and sub companies. The elements of the networkmay be connected by a local area network or a wide area network such asthe Internet. A virtual or real central location 102, or a distributednetwork of locations, may be created to operate, and supervise the useof, the processing mechanisms described herein. The central location 102has access to the QB files of a plurality of companies 120, 140, 160,and the affiliate or child companies of these companies 142, 144, 162,and 164.

Thus, for example, the central location may be at an accounting firmwith a great number of client companies, each company having at leastone QB file. The company 120, for example, may be a florist shop. Theparent business 140 may be a mobile phone store with a south store 142and a north store 144, where the north and south stores may be locatedat different locations within a city or state. The parent business 160may be a law firm with its principal office in New York, and withoffices in Chicago, 162, and in Atlanta, 164.

Note that in the case of related businesses 140, 142, and 144, eachbusiness' respective QB file is located at the same location as thebusiness. In contrast, in the case of the related businesses 160, 162,and 164, each business' respective QB file is located at a centrallocation at business site 160. Note also, that the QB files of business120 are located offsite. Virtually any business, having virtually anycomplex relationships of its sub businesses, may use separate instancesof QB at the respective locations of the businesses and sub businesses.

In one embodiment, the central location 102 may access the QB files fromthese businesses and sub businesses and store them in a database of QBfiles 104. A contributor 106 may have access to the system only to theextent necessary to maintain the system. A network administrator 108 mayset passwords, control access to the system, and perform otheradministrative functions. For example, the network administrator 108 maygrant viewing access to a client viewer 110, enabling the viewer 110 toview aggregated QB data. To enable access to the data, a graphical userinterface and a keyboard and a mouse may be provided, as is known in theart.

In one embodiment, QB data is obtained from the plurality of companies,120, 140, 142, 144, 160, 162, and 164, by at least one server 1000 via aQB synchronization application 1002. The server 1000 may be located atany location connected to the network. In particular, the server 1000may be located at the central location 102 or at a separate location.The QB synch application 1002 may be downloaded over the network to thebusiness locations where the QB files are located. Thus, for example,the QB synch application 1002 may be downloaded to locations 104, 140142, 144, and 160, as well as to the central location 102. Thesynchronization application 1002 may reside at the local QB filelocations 104, 140 142, 144, and 160, and may operate to uploadtransactional data from the QB files at the locations 104, 140 142, 144,and 160. The upload of transactional data may be scheduled by anadministrator to occur as often as desired, for example, daily, or theupload can occur upon entry of a command by the administrator.

FIG. 2B depicts a flow chart for setting up QB relationships foruploading QB files to a central location and aggregating data from aplurality of QB files of client companies and sub companies. First, anadministrator of accounts will set up an account and company profile, at202. Then, the administrator may create a parent business or clientaccount, at 204. Once a parent client is created, the administrator maycreate sub businesses or sub clients of the parent client, at 206.

The administrator may then authorize certain contributors to have accessto the system, at 208. An authorized contributor may install a QBsynchronization application at a local computer, at 210. The QBsynchronization application interfaces with the local QB files oftransaction data, and transmits the data from such files to the centrallocation. Once synchronization takes place, authorized viewers maycompile and review business reports that reflect data aggregated from QBfiles of different locations and companies, at 212.

FIG. 3 shows a table of data acquired from the files of data for companyA shown in FIG. 1A and for company B as shown in FIG. 1B, usingStructured Query Language (SQL). Acquiring data using SQL is timeconsuming and complex SQL commands are required to acquire the desireddata in a desired form.

FIG. 4 depicts a table of parent accounts extracted from the dataassociated with company A and company B. A list of parent accounts fromthe different QB files is acquired using, for example, an SQL command orcommand in C, C++, or C# or other known programming language. Eachparent account is associated with an account identification (ID). Aswill be discussed above, an account ID serves as a key to a hash table.

FIG. 5 depicts a collection of hash tables and their collection of subaccounts, each key in the hash table is an account ID for the data fromcompanies A and B. Each hash table entry corresponds to a differentparent account or sub account. For example, referring to FIG. 4, a firstparent account named hotel is from company A and has an account ID of 1.A second parent account named hotel is from company B and has an accountID of 2. Thus, like-named parent accounts are initially assigneddifferent account IDs. In FIG. 5, the collection for account ID 1(key=1) has a row for each of the two sub accounts of the parentaccount, Hotel, of company A. Each sub account of this table is assigneda unique account ID. The collection for account ID 2 (key=2) has a rowfor each of the two sub accounts of the parent account, Hotel, ofcompany B. Each sub account of this collection is assigned a uniqueaccount ID.

Further, an entry in the hash table is created for each sub account'ssub accounts. The key of a sub account's collection of sub accounts isthe account ID of the sub account, as shown in the table of key=1 andthe table of key=2. For example, the table of key=5 is the table of thesub account named Motel 6 from the table of key=1. The entry of key=5has a row for each sub sub account (Dallas and Houston) of the subaccount named Motel 6 of account ID=5. The entry of key=6 is thecollection of the sub accounts of the parent named Motel 6 from theentry of key=2. And so forth. Note that there is no entry with key=3,for example, because the subaccount named Hyatt of the table of key=1has no sub sub accounts.

Not shown in FIG. 5 are the columns of data extracted from the columnsof the Tables of data from company A (FIG. 1A) and company B (FIG. 1B).An actual hash table will have an entry for each such column.

One embodiment, therefore, is a QB data aggregation system, having a webserver connected to a network of computers having QB files. The QB filesare stored in a database format at each computer. The web server hasmemory to store data obtained from the QB files. The web server also hasa processor configured to process and aggregate the data of the variousQB files. The processor is configured to identify like-named parentaccounts of data from each computer. For each like-named parent account,the data of sub accounts of the parent accounts is aggregated. Theaggregated data is stored in a hash table from which a report may becreated to display the data according to parent account and sub account.Each entry of the hash table corresponds to data of a different-namedparent account. An entry has a collection of rows for each sub accountof a parent account, and has columns of aggregated data associated witheach sub account. Each column may correspond to data associated with atime frame, for example, monthly data. The data may be an aggregateprofit and loss table, for example, showing the aggregate profit andloss data for a plurality of companies. As another example, theaggregate data may be presented in a chart of accounts.

FIG. 6 depicts a flow chart for assigning IDs and creating hash tables.At 602, the system assigns an ID to each differently-named parentaccount. An entry in the hash table is created for each parent accountID, at 604. The contents of an entry has a collection of rows for eachdifferent sub account of the parent. The key of the entry is the parentaccount ID. IDs are also assigned to each sub account of a parentaccount, at 606. An entry in the hash table is created for each subaccount ID, at 608, Each entry in a hash table for a sub account is acollection of rows which represent a different sub sub account of thesub account to which the collection is associated.

FIG. 7 depicts a flow chart for identifying and aggregating data oflike-named accounts. At 702, like named parent accounts are identified.At 704, for each like-named parent account, like-named sub accounts ofthe like-named parent accounts are identified. At 706, data for eachlike-named sub account of each like-named parent account are aggregated.At 708 a report, such as the table of FIG. 1C, is created upon requestby an authorized contributor or the system administrator.

An example of pseudo code for implementing the methods described hereinis as follows:

For every account returned: If account is top-level parent, place in aseparate collection Otherwise, place in the collection stored in thehash table at key of parent account ID For every parent in result: Forevery other parent in result: If name matches, combine parent accountsFor every parent account that was combined: Get collection of childrenfrom hash table For every child of this parent: For every other child ofthis parent: If name matches, combine child accounts

The first loop, in C#, is a quick process because the loop does notperform a comparison of account IDs. Rather, the loop merely retrieveseach account and checks if an account ID exists or not, which does notrequire subtraction. The subsequent loops in C# compares accountsagainst a sub set of accounts that only match parents. Only childrenthat belong to parents that were combined will be compared. This isbecause if the parent accounts did not match, then children accountswill not match. This drastically reduces the number of comparisons thatare required to aggregate the data. In contrast, using SQL requires acollection for every parent account, rather than merely everydifferently-named parent account. That is, even with the bulk operationsthat SQL can perform for locating matching children, it cannot performbulk operations needed to recursively aggregate matching children; forthis, CPU intensive custom looping would need to be performed. A reasonthat using C# is faster is that fewer comparisons need be made.

Another illustrative embodiment includes a machine-readable mediumembodying machine-readable instructions that, when executed by aprocessor, cause the processor to obtain data from each of a pluralityof computers in a network, the data being organized into one or moreparent accounts. The processor is further caused by the instructions toaggregate the data from like-named parent accounts. The processor mayfurther be caused to form a hash table with entries being a collectionof sub accounts for each differently-named parent account, thecollection including aggregate data associated with the parent accountname In some embodiments, the processor is further caused to obtain datafrom each of the plurality of computers in the network, the data beingorganized into one or more sub accounts. The processor may furtheraggregate data of one or more like-named sub accounts of like-namedparent accounts. The processor may also form an entry in the hash tablefor each differently-named sub account, the entry including aggregatedata associated with the sub account name. An item in the collection isa row that has data associated with a sub sub account name. The processmay continue further in like manner for sub sub accounts, and so forth.

Various changes, substitutions and alterations can be made to theembodiments described herein without departing from the scope of theappended claims. An embodiment may achieve multiple objectives, but notevery embodiment falling within the scope of the attached claims willachieve every objective. Moreover, the scope of the present applicationis not intended to be limited to the particular embodiments of theprocess, machine, manufacture, composition of matter, means, methods andsteps described in the specification. One of ordinary skill in the artwill readily appreciate from this disclosure that processes, machines,manufacture, compositions of matter, means, methods, or steps, presentlyexisting or later to be developed are equivalent to, and fall within thescope of what is claimed. Accordingly, the appended claims are intendedto include within their scope such processes, machines, manufacture,compositions of matter, means, methods, or steps.

What is claimed is:
 1. A method of aggregating data in a computerizedapparatus, the method comprising: obtaining a plurality of accountingrecords from a plurality of physical locations; storing the plurality ofaccounting records in a database associated with the computerizedapparatus; extracting the plurality of accounting records from thedatabase, each of the accounting records comprising an accountidentifier, an account name, and a parent account identifier, the parentaccount identifier comprising either: a top level account identifier, ora non-top level account identifier; for each of the plurality ofaccounting records, assigning that accounting record in the computerizedapparatus as a top-level parent accounting record in a hierarchicalstructure when that accounting record comprises the top level accountidentifier, otherwise placing that accounting record into a datastructure associated with the computerized apparatus; combining thosetop-level parent accounting records that share a common name using thecomputerized apparatus; for each of the plurality of accounting recordsplaced into the data structure, when the parent account identifierassociated with each accounting record matches the account identifier ofthe top-level parent accounting record, combining that accounting recordinto the hierarchical structure with its associated top-level parentaccounting record using the computerized apparatus, otherwise leavingthat accounting record in the data structure; for each of the pluralityof accounting records whose parent account identifier does not match theaccount identifier of the top-level parent accounting record, combiningthat accounting record into the hierarchical structure when the parentaccount identifier for that accounting record matches the accountidentifier of an accounting record that has already been placed into thehierarchical structure using the computerized apparatus, otherwiseleaving that accounting record in the data structure; repeating the actof combining the accounting record into the hierarchical structure usingthe computerized apparatus until no accounting records exist in the datastructure; and generating a report using the computerized apparatusbased at least on the hierarchical structure created.
 2. The method ofclaim 1, wherein the combining of that accounting record into thehierarchical structure is accomplished via a recursive aggregation. 3.The method of claim 1, wherein a determination of whether the parentaccount identifier associated with each account record matches theaccount identifier of the top-level parent accounting record occurs viaa general purpose programming language rather than a query language. 4.The method of claim 3, wherein the extracting the plurality ofaccounting records from the database occurs via the query language. 5.The method of claim 1, further comprising prior to generating thereport, verifying authorization of a user to generate the report.
 6. Themethod of claim 1, wherein the obtaining of the plurality of accountingrecords from the plurality of physical locations comprises synchronizingthe plurality of accounting records between a central location and theplurality of physical locations.
 7. Computerized apparatus comprising anon-transient storage medium having a plurality of computer readableinstructions disposed thereon, the instructions configured to, whenexecuted: obtain a plurality of accounting records from a plurality ofphysical locations; store the plurality of accounting records in adatabase associated with the computerized apparatus; extract theplurality of accounting records from the database, each of theaccounting records comprising an account identifier, an account name,and a parent account identifier, the parent account identifiercomprising either: a top level account identifier, or a non-top levelaccount identifier; for each of the plurality of accounting records,assign that accounting record in the computerized apparatus as atop-level parent accounting record in a hierarchical structure when thataccounting record comprises the top level account identifier, otherwiseplace that accounting record into a data structure associated with thecomputerized apparatus; combine those top-level parent accountingrecords that share a common name using the computerized apparatus; foreach of the plurality of accounting records placed into the datastructure, when the parent account identifier associated with eachaccounting record matches the account identifier of the top-level parentaccounting record, combine that accounting record into the hierarchicalstructure with its associated top-level parent accounting record usingthe computerized apparatus, otherwise leaving that accounting record inthe data structure; for each of the plurality of accounting recordswhose parent account identifier does not match the account identifier ofthe top-level parent accounting record, combine that accounting recordinto the hierarchical structure when the parent account identifier forthat accounting record matches the account identifier of an accountingrecord that has already been placed into the hierarchical structureusing the computerized apparatus, otherwise leave that accounting recordin the data structure; repeat the combination of the accounting recordinto the hierarchical structure until no accounting records exist in thedata structure; and generate a report using the computerized apparatusbased at least on the hierarchical structure created.
 8. Thecomputerized apparatus of claim 7, wherein plurality of accountingrecords are stored in a plurality of QuickBooks files.
 9. Thecomputerized apparatus of claim 7, wherein the instructions that make adetermination of whether the parent account identifier associated witheach account record matches the account identifier of the top-levelparent accounting record occurs via a general purpose programminglanguage rather than a query language.
 10. The computerized apparatus ofclaim 7, wherein the instructions are further configured to, whenexecuted: synchronize the plurality of accounting records between acentral location and the plurality of physical locations.
 11. Thecomputerized apparatus of claim 7, wherein placement of accountingrecords into the data structure comprises creation of a hash table entryfor each accounting record that comprises a non-top level accountidentifier, each hash table entry accessible via a key, the keycomprising the parent account identifier of the accounting record.